package com.alibaba.cloud.utils;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.create.table.ColDataType;
import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.create.table.Index;

import org.apache.commons.lang3.StringUtils;

import lombok.extern.slf4j.Slf4j;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.io.StringReader;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

@Slf4j
public class Mysql2DorisSql {
	public static void main(String[] args) throws JSQLParserException {
		try {
			List<String> list = Mysql2DorisSql.readFile("E://iam.sql");
			List<String> createSqlList = new ArrayList<>();
			StringBuilder finalSqlBuilder = new StringBuilder();
			for (String sql : list) {
				StringBuilder sqlBuilder = new StringBuilder();
				StringBuilder prefixBuilder = new StringBuilder();
				CCJSqlParserManager parser = new CCJSqlParserManager();
				// 列字段名称跟信息。
				List<ColumnDefinition> columnList = null;
				// 表名字
				String tableName = "";
				// 主键名称
				List<String> pkColumnsList = null;
				if (sql.contains("AUTO_INCREMENT")) {
					sql = sql.replaceAll("AUTO_INCREMENT", "");
				}
				log.info("原始SQL 为：{}", sql);
				Statement statement = parser.parse(new StringReader(sql));
				if (statement instanceof CreateTable createtable) {
					tableName = createtable.getTable().getName();
					List<Index> indexes = createtable.getIndexes();
					pkColumnsList = indexes.get(0).getColumnsNames();
					columnList = createtable.getColumnDefinitions();
				} else {
					// 如果不是建表语句则直接跳过
					break;
				}
				// 建表语句
				prefixBuilder.append("CREATE TABLE ").append(tableName).append(" ").append("(").append("\n");
				// 先将主键字段放在第一位 其他 全部变成varchar()
				for (int i = 0; i < columnList.size(); i++) {
					String columnString = columnList.get(i).toString();
					String columnName = columnList.get(i).getColumnName();
					ColDataType columnDataType = columnList.get(i).getColDataType();
					// 字段的类型
					String dataType = columnDataType.getDataType();
					log.info("当前字段：{} 类型 为：{}", columnName, dataType);
					// 是主键的情况下 主键放最前面。
					if (pkColumnsList.contains(columnName)) {
						if ("varchar".equalsIgnoreCase(dataType)) {
							List<String> argumentsStringList = columnDataType.getArgumentsStringList();
							Integer typeValueTriple = Integer.valueOf(argumentsStringList.get(0)) * 5;
							prefixBuilder.append(columnName).append(" ").append(dataType).append("(")
									.append(typeValueTriple).append(")");
						} else if ("datetime".equalsIgnoreCase(dataType) || "date".equalsIgnoreCase(dataType)) {
							prefixBuilder.append(columnName).append(" ").append(dataType);
						} else {
							prefixBuilder.append(columnString);
						}
						prefixBuilder.append(",").append("\n");
					} else {
						// 如果是varchar 类型就放大三倍
						if ("varchar".equalsIgnoreCase(dataType)) {
							List<String> argumentsStringList = columnDataType.getArgumentsStringList();
							Integer typeValueTriple = Integer.valueOf(argumentsStringList.get(0)) * 5;
							sqlBuilder.append(columnName).append(" ").append(dataType).append("(")
									.append(typeValueTriple).append(")");
						} else if ("datetime".equalsIgnoreCase(dataType) || "date".equalsIgnoreCase(dataType)) {
							sqlBuilder.append(columnName).append(" ").append(dataType);
						} else {
							sqlBuilder.append(columnString);
						}
						sqlBuilder.append(",").append("\n");
					}
				}
				if (sqlBuilder.length() > 0) {
					sqlBuilder.delete(sqlBuilder.length() - 2, sqlBuilder.length());
					sqlBuilder.append("\n").append(")").append("\n");
				} else {
					// 字段全是主键
					log.info("当前表只有主键");
				}
				sqlBuilder.append("UNIQUE KEY(");
				for (int i = 0; i < pkColumnsList.size(); i++) {
					sqlBuilder.append(pkColumnsList.get(i) + ",");
				}
				sqlBuilder.delete(sqlBuilder.length() - 1, sqlBuilder.length());
				sqlBuilder.append(")").append("\n").append("DISTRIBUTED BY HASH(");
				for (int i = 0; i < pkColumnsList.size(); i++) {
					sqlBuilder.append(pkColumnsList.get(i) + ",");
				}
				sqlBuilder.delete(sqlBuilder.length() - 1, sqlBuilder.length());
				sqlBuilder.append(")").append(" ").append("BUCKETS 10").append("\n")
						.append("PROPERTIES('replication_num'='3')");
				prefixBuilder.append(sqlBuilder);
				createSqlList.add(prefixBuilder.toString());
				log.info("当前表的建表SQL ={} ", createSqlList.toString());
				finalSqlBuilder.append(prefixBuilder.toString() + ";\n\n");
			}
			log.info("所有表的最终SQL = {}", finalSqlBuilder.toString());
		} catch (IOException e) {
			log.error("SQL 生成失败", e);
		}
	}

	/**
	 * @Description: 读取SQL 文件
	 * @author: zhb1316783262@163.com
	 * @date: 2024年7月19日下午3:41:45
	 */
	public static List<String> readFile(String fileName) throws IOException {
		List<String> sqlList = new ArrayList<>();
		try (BufferedReader br = new BufferedReader(new FileReader(fileName, StandardCharsets.UTF_8))) {
			String line;
			StringBuilder sqlBuffer = new StringBuilder();
			while (Objects.nonNull(line = br.readLine())) {
				if (StringUtils.isEmpty(line) || line.trim().startsWith("--") || line.trim().startsWith("DROP")) {
					continue;
				}
				// remove comment
				if (line.contains("--")) {
					line = line.substring(0, line.indexOf("--"));
				}
				sqlBuffer.append(line);
				sqlBuffer.append("\n");
				if (line.endsWith(";")) {
					// add sql to sqlList
					String tmpSql = sqlBuffer.toString();
					// remove last ";"
					tmpSql = tmpSql.substring(0, tmpSql.lastIndexOf(";"));
					sqlList.add(tmpSql);
					// remove StringBuilder
					sqlBuffer.delete(0, sqlBuffer.length());
				}
			}
			// if last sql sentence not end with ";"
			if (sqlBuffer.length() != 0) {
				sqlList.add(sqlBuffer.toString());
			}
		}
		return sqlList;
	}
}